package com.xuzimian.globaldemo.office.excel.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.google.common.base.Strings;
import com.xuzimian.globaldemo.office.excel.pojo.ExcelAddress;
import com.xuzimian.globaldemo.office.excel.pojo.ExcelCellData;
import com.xuzimian.globaldemo.office.excel.pojo.ExcelTemplate;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel工具类
 *
 * @author xuzimian
 */
public class ExcelUtil {

    public static void copyExcelWorkBookAndFillData(String tempFilePath, String outPutFilePath,
                                                    List<ExcelCellData> cellDatas) throws IOException {

        ExcelTemplate excelTemplate = ExcelUtil.copyAndCreateSheet(tempFilePath, cellDatas.size());
        Workbook wb = excelTemplate.getOutputWB();

        Sheet sheet = wb.getSheet(excelTemplate.getSheetName());

        int n = 0;
        for(ExcelCellData d : cellDatas){
            int offsetRow = n * excelTemplate.getSourceSheetLastRowNum();
            d.getDatas().asMap().forEach((firstkEy, listValue) -> {
                listValue.stream().forEach(celldata -> {
                    Map<String, ExcelAddress> excelAddressMap = excelTemplate.getExcelReplaceAddress()
                            .getAddress(firstkEy);
                    //System.out.println(firstkEy + ":" + excelAddress);
                    excelAddressMap.forEach((secondKey, address) -> {
                        address.setOffseRow(offsetRow);
                        secondKey = Strings.isNullOrEmpty(secondKey) ? "null" : secondKey;
                        sheet.getRow(address.getRow() + offsetRow).getCell(address.getColumn())
                                .setCellValue(celldata.get(secondKey));
                    });
                });
            });
            n++;
        }
        ExcelUtil.outputExcelFile(wb, outPutFilePath);
    }

    /***
     * 复制excel模板sheet
     *
     * @param fromFilePth
     * @param fromSheetName
     * @return
     * @throws IOException
     */
    public static ExcelTemplate copyAndCreateSheet(String fromFilePth, String fromSheetName) throws IOException {
        ExcelTemplate excelTemplate = new ExcelTemplate(fromSheetName);
        excelTemplate.sourceWB(openWorkbook(fromFilePth)).outputWB(createXSSFWorkbook(fromSheetName));
        copySheet(excelTemplate);
        return excelTemplate;
    }

    /**
     * 复制excel模板sheet
     *
     * @param fromFilePth
     * @return
     * @throws IOException
     */
    public static ExcelTemplate copyAndCreateSheet(String fromFilePth, int repeatNum) throws IOException {

        Workbook fromWorkbook = openWorkbook(fromFilePth);
        String fromSheetName = fromWorkbook.getNumberOfSheets() > 0 ? fromWorkbook.getSheetAt(0).getSheetName() : null;
        if (Strings.isNullOrEmpty(fromSheetName)) {
            throw new NullPointerException();
        }
        ExcelTemplate excelTemplate = new ExcelTemplate(fromSheetName);
        excelTemplate.sourceWB(fromWorkbook).outputWB(
                Strings.isNullOrEmpty(fromSheetName) ? createXSSFWorkbook() : createXSSFWorkbook(fromSheetName));

        copySheetAndRepeatTemplate(excelTemplate, repeatNum);

        return excelTemplate;
    }

    public static void copySheetAndRepeatTemplate(ExcelTemplate excelTemplate, int repeatNum) {
        Sheet sourcSheet = excelTemplate.getSourceSheet();
        mergerRegionByRepeat(sourcSheet, excelTemplate.getOutputSheet(), repeatNum);
        copyRowByRepeat(excelTemplate, repeatNum);
    }

    public static void copySheet(ExcelTemplate excelTemplate) {
        mergerRegion(excelTemplate.getSourceSheet(), excelTemplate.getOutputSheet());
        copyRow(excelTemplate);
    }

    /***
     * 复制行
     * @param excelTemplate
     */
    public static void copyRow(ExcelTemplate excelTemplate) {
        Iterator rowIterator = excelTemplate.getSourceSheet().rowIterator();
        while (rowIterator.hasNext()) {
            Row tmpRow = (Row) rowIterator.next();
            Row newRow = excelTemplate.getOutputSheet().createRow(tmpRow.getRowNum());
            // 行高设置
            newRow.setHeight(tmpRow.getHeight());
            // 单元格复制
            tmpRow.forEach(d -> {
                excelTemplate.getReplaceCell(copyCell(d, newRow.createCell(d.getColumnIndex())));
            });
        }
    }

    public static void copyRowByRepeat(ExcelTemplate excelTemplate, int repeatNum) {
        Sheet sourcSheet = excelTemplate.getSourceSheet();
        Iterator rowIterator = sourcSheet.rowIterator();
        int lastRowNum = sourcSheet.getLastRowNum();
        excelTemplate.setSourceSheetLastRowNum(lastRowNum);
        while (rowIterator.hasNext()) {
            Row tmpRow = (Row) rowIterator.next();
            //模版占位符地址集生成
            tmpRow.forEach(d -> excelTemplate.getReplaceCell(d));
            for (int n = 0; n < repeatNum; n++) {
                //模版行复制
                Row newRow = excelTemplate.getOutputSheet().createRow(tmpRow.getRowNum() + (lastRowNum * n));
                // 行高设置
                newRow.setHeight(tmpRow.getHeight());
                // 单元格复制
                tmpRow.forEach(d -> {
                    copyCell(d, newRow.createCell(d.getColumnIndex()));
                });
            }
        }


    }

    /**
     * 单元格合并复制
     *
     * @param fromSheet
     * @param toSheet
     */
    public static void mergerRegion(Sheet fromSheet, Sheet toSheet) {
        int sheetMergerCount = fromSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress mergedRegionAt = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(mergedRegionAt);
        }
    }

    /***
     * 单元格合并复制
     *
     */
    public static void mergerRegionByRepeat(Sheet fromSheet, Sheet toSheet, int repeatNum) {
        int sheetMergerCount = fromSheet.getNumMergedRegions();
        int lastRowNum = fromSheet.getLastRowNum();
        for (int n = 0; n <= repeatNum; n++) {
            for (int i = 0; i < sheetMergerCount; i++) {
                CellRangeAddress mergedRegionAt = fromSheet.getMergedRegion(i);
                mergedRegionAt.setFirstRow(mergedRegionAt.getFirstRow() + (n * lastRowNum));
                mergedRegionAt.setLastRow(mergedRegionAt.getLastRow() + (n * lastRowNum));
                toSheet.addMergedRegion(mergedRegionAt);
            }
        }
    }

    /**
     * 行的所有单元格复制
     *
     * @param fromRow
     * @param toRow
     */
    public static void copyRowAllCell(Row fromRow, Row toRow) {
        for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            Cell tmpCell = (Cell) cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            newCell.getSheet().setColumnWidth(newCell.getColumnIndex(),
                    tmpCell.getSheet().getColumnWidth(tmpCell.getColumnIndex()));
            copyCellType(tmpCell, newCell);
        }
    }

    public static Cell copyCell(Cell sourceCell, Cell newCell) {
        newCell.getSheet().setColumnWidth(newCell.getColumnIndex(),
                sourceCell.getSheet().getColumnWidth(sourceCell.getColumnIndex()));
        copyCellType(sourceCell, newCell);
        return newCell;
    }

    /**
     * 复制单元格类型
     *
     * @param srcCell
     * @param distCell
     */
    public static void copyCellType(Cell srcCell, Cell distCell) {
        CellStyle newstyle = distCell.getSheet().getWorkbook().createCellStyle();
        newstyle.cloneStyleFrom(srcCell.getCellStyle());
        // 样式
        distCell.setCellStyle(newstyle);
        // 评论
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }

        // 不同数据类型处理
        CellType srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);

        if (srcCellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(srcCell)) {
                distCell.setCellValue(srcCell.getDateCellValue());
            } else {
                distCell.setCellValue(srcCell.getNumericCellValue());
            }
        } else if (srcCellType == CellType.STRING) {
            distCell.setCellValue(srcCell.getRichStringCellValue());
        } else if (srcCellType == CellType.BOOLEAN) {
            distCell.setCellValue(srcCell.getBooleanCellValue());
        } else if (srcCellType == CellType.ERROR) {
            distCell.setCellErrorValue(srcCell.getErrorCellValue());
        } else if (srcCellType == CellType.FORMULA) {
            distCell.setCellFormula(srcCell.getCellFormula());
        }
    }

    /**
     * 读取Excel文档
     *
     * @param filePath
     * @param password
     * @return
     * @throws IOException
     */
    public static Workbook openWorkbook(String filePath, String password) throws IOException {
        InputStream in = new FileInputStream(filePath);
        return openWorkbook(in, password);
    }

    /**
     * 读取Excel文档
     *
     * @param filePath
     * @return
     * @throws IOException
     */
    public static Workbook openWorkbook(String filePath) throws IOException {
        InputStream in = new FileInputStream(filePath);
        return openWorkbook(in);
    }

    /**
     * 读取Excel文档
     *
     * @param in
     * @return
     * @throws IOException
     */
    public static Workbook openWorkbook(InputStream in) throws IOException {
        return WorkbookFactory.create(in);
    }

    /**
     * @param in
     * @param password
     * @return
     * @throws IOException
     */
    public static Workbook openWorkbook(InputStream in, String password) throws IOException {
        return WorkbookFactory.create(in, password);
    }

    /**
     * 创建Excel2007以上版本的（xlsx）文档对象
     *
     * @param sheetNames
     * @return
     */
    public static XSSFWorkbook createXSSFWorkbook(String[] sheetNames) {
        XSSFWorkbook xb = new XSSFWorkbook();

        for (String sheetName : sheetNames) {
            xb.createSheet(sheetName);
        }

        return xb;
    }

    /**
     * 创建Excel2007以上版本的（xlsx）文档对象
     *
     * @param sheetName
     * @return
     */
    public static XSSFWorkbook createXSSFWorkbook(String sheetName) {
        String[] sheetNames = {sheetName};
        return createXSSFWorkbook(sheetNames);
    }

    /**
     * 创建Excel2007以上版本的（xlsx）文档对象
     *
     * @return
     */
    public static XSSFWorkbook createXSSFWorkbook() {
        return createXSSFWorkbook("sheet0");
    }

    /**
     * 创建Excel2003版本的（xls）文档对象
     *
     * @param sheetNames
     * @return
     */
    public static HSSFWorkbook createHSSFWorkbook(String[] sheetNames) {
        HSSFWorkbook wb = new HSSFWorkbook();
        for (String sheetName : sheetNames) {
            wb.createSheet(sheetName);
        }
        return wb;
    }

    /**
     * 创建Excel2003版本的（xls）文档对象
     *
     * @return
     */
    public static HSSFWorkbook createHSSFWorkbook() {
        return createHSSFWorkbook("sheet0");
    }

    /**
     * 创建Excel2003版本的（xls）文档对象
     *
     * @param sheetName
     * @return
     */
    public static HSSFWorkbook createHSSFWorkbook(String sheetName) {
        String[] sheetNames = {sheetName};
        return createHSSFWorkbook(sheetNames);
    }

    /**
     * 输出excel文档
     *
     * @param wb
     * @param filePathe
     * @throws IOException
     */
    public static void outputExcelFile(Workbook wb, String filePathe) throws IOException {
        // 输出Excel文件
        FileOutputStream output = new FileOutputStream(filePathe);
        wb.write(output);
        output.flush();
    }
}